package bysj.dyz.dao;

import bysj.dyz.bean.Good;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

public class GoodDao {
    /**
     * 添加失物
     * @param good
     * @return
     * @throws SQLException
     */
    public boolean addGood(Good good) throws SQLException {
        ComboPooledDataSource dataSource = new ComboPooledDataSource();
        QueryRunner queryRunner = new QueryRunner(dataSource);
        String sql="insert into good values(null,?,?,?,?,?,?,?,?,?,?)";
        int update = queryRunner.update(sql, good.getGoodname(), good.getGoodplace(), good.getGoodtime(), good.getGoodexplain(), good.getGoodtype(), good.getGoodstate(),good.getGoodpicture(), good.getFabuid(), good.getFabucontact(), good.getFabutime());

        if (update>0){
            return true;
        }else {
            return false;
        }
    }

    /**
     * 按类型查询
     * @param goodtype
     * @return
     * @throws SQLException
     */
    public List<Good> selectGood(String goodtype) throws SQLException {
        ComboPooledDataSource dataSource = new ComboPooledDataSource();
        QueryRunner queryRunner = new QueryRunner(dataSource);
        String sql="select * from good where goodtype=?";
        List<Good> query = queryRunner.query(sql, new BeanListHandler<Good>(Good.class), goodtype);
        return query;
    }


    /**
     * 按类型查询总条数
     * @return
     */
    public int queryCount(String goodtype) throws SQLException {

        ComboPooledDataSource dataSource = new ComboPooledDataSource();
        QueryRunner queryRunner = new QueryRunner(dataSource);
        String sql="select count(*) from good where goodtype=?";
        Long query = queryRunner.query(sql, new ScalarHandler<>(), goodtype);

        return  query.intValue();
    }

    /**
     * 按类型分页查询
     * @param goodtype
     * @param startPosition
     * @param currentCount
     * @return
     * @throws SQLException
     */
    public List<Good> pageselectgod(String goodtype, int startPosition, int currentCount) throws SQLException {
        ComboPooledDataSource dataSource = new ComboPooledDataSource();
        QueryRunner queryRunner = new QueryRunner(dataSource);
        String sql="select * from good where goodtype=? limit ?,?";
        List<Good> query = queryRunner.query(sql, new BeanListHandler<Good>(Good.class), goodtype, startPosition, currentCount);

        return  query;
    }


    /**
     * 查询所有失物总条数
     * @return
     */

    public int queryCount() throws SQLException {
        ComboPooledDataSource dataSource = new ComboPooledDataSource();
        QueryRunner queryRunner = new QueryRunner(dataSource);
        String sql="select count(*) from good";
        Long query = queryRunner.query(sql, new ScalarHandler<>());

        return  query.intValue();

    }

    /**
     * 分页查询所有失物
     * @param startPosition
     * @param currentCount
     * @return
     */
    public List<Good> pageselectgod(int startPosition, int currentCount) throws SQLException {
        ComboPooledDataSource dataSource = new ComboPooledDataSource();
        QueryRunner queryRunner = new QueryRunner(dataSource);
        String sql="select * from good  order by fabutime desc limit ?,?";
        List<Good> query = queryRunner.query(sql, new BeanListHandler<Good>(Good.class),startPosition,currentCount);

        return  query;
    }


    /**
     * 更改good表中物品的状态
     * @param goodstate
     */
    public boolean updateGoodState(String goodstate,int applygoodid) throws SQLException {
        ComboPooledDataSource dataSource = new ComboPooledDataSource();
        QueryRunner queryRunner = new QueryRunner(dataSource);
        String sql="update good set goodstate=?  where  goodid=?";
        int update = queryRunner.update(sql, goodstate, applygoodid);

        if (update>0){
            return  true;
        }else {
            return false;
        }
    }


    /**
     * 按发布者id查询总条数
     * @param userid
     * @return
     */
    public int queryCount(int userid) throws SQLException {
        ComboPooledDataSource dataSource = new ComboPooledDataSource();
        QueryRunner queryRunner = new QueryRunner(dataSource);
        String sql="select count(*) from good where fabuid=?";
        Long query = queryRunner.query(sql, new ScalarHandler<>(),userid);

        return  query.intValue();

    }

    /**
     * 按发布者id分页查询失物
     * @param userid
     * @param startPosition
     * @param currentCount
     * @return
     */
    public List<Good> pagefabugood(int userid, int startPosition, int currentCount) throws SQLException {
        ComboPooledDataSource dataSource = new ComboPooledDataSource();
        QueryRunner queryRunner = new QueryRunner(dataSource);
        String sql="select * from good where fabuid=? limit ?,?";
        List<Good> query = queryRunner.query(sql, new BeanListHandler<Good>(Good.class),userid , startPosition, currentCount);

        return  query;
    }

    /**
     * 删除失物
     * @param goodid
     */
    public boolean deletegood(String goodid) throws SQLException {
        ComboPooledDataSource dataSource = new ComboPooledDataSource();
        QueryRunner queryRunner = new QueryRunner(dataSource);
        String sqlchild="delete from apply where applygoodid=?";
        int updatechild = queryRunner.update(sqlchild, goodid);
        String sql="delete from good where goodid=?";
        int update = queryRunner.update(sql, goodid);
        if(update>0){
            return  true;
        }else {
            return  false;
        }
    }

    /**
     * 更新失物信息
     * @param good
     * @param goodid
     */
    public boolean updategood(Good good, String goodid) throws SQLException {
        ComboPooledDataSource dataSource = new ComboPooledDataSource();
        QueryRunner queryRunner = new QueryRunner(dataSource);
        String sql="update good set goodtype=?,goodname=?,goodexplain=?,goodtime=?,goodplace=?,fabucontact=?,goodpicture=? where goodid=?";
        int update = queryRunner.update(sql, good.getGoodtype(), good.getGoodname(), good.getGoodexplain(), good.getGoodtime(), good.getGoodplace(), good.getFabucontact(), good.getGoodpicture(), goodid);
        if (update>0){
            return  true;
        }else {
            return  false;
        }
    }
}
